<?php
	define('CONFIG_FILE',true);
    include '../config.php';
    
	define('DB_FILE',true);
	require_once '../Tool/DB/SqlDBManager.class.php';
	require_once '../Classes/PHPExcel.php';
	require_once '../Tool/Common/PrepareInput.php';
	
	//Start session
	session_start();
	if (empty($_SESSION["userType"])) {
		$url="../Index.php";
		include_once '../Tool/Common/Redirect.php';
		exit();
	}
	
	//Create DBManager Object
	$sqlDBManager = new SqlDBManager();

	if(!empty($_SESSION['category'])){
		$category = prepareInput($_SESSION['category']);
	}
	if(!empty($_SESSION['date'])){
		$date = date('Y-m-d', strtotime(prepareInput($_SESSION['date'])));
		$month = date('F', strtotime(prepareInput($_SESSION['date'])));
		$year = date('Y', strtotime(prepareInput($_SESSION['date'])));
	}

	//Validate input
    if(!preg_match("/^[A-Za-z]*$/",$category)){
        echo "<font color=red>Invalid Category Type.</font><br/>";
        exit();
    }
    if(!preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/",$date)){
        echo "<font color=red>Invalid Date Format.</font><br/>";
        exit();
    }
	
    //retrieve NRIC list
	if (!empty($_GET['queryString'])){
		$queryString=$_GET['queryString'];
		$nricList = $_SESSION[$queryString];
	}

	//Create PHPExcel Object
	$objPHPExcel = new PHPExcel();
	$objPHPExcel->setActiveSheetIndex(0);
	$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
	$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
	$objPHPExcel->getActiveSheet()->getStyle('A3:Z3')->getFont()->setBold(true);
	
	//Excel file name
	$objPHPExcel->getProperties()->setTitle("Eldercare Individual Report in " . $month . "/" . $year);
	
	//Title of Excel sheet
	$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Eldercare Individual Report in '. $month . '/' . $year);
	$objPHPExcel->getActiveSheet()->mergeCells('A1:N1');
	$objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

	//First column Name
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(9);
	$objPHPExcel->getActiveSheet()->SetCellValue('A3', 'S/No');
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13);
	$objPHPExcel->getActiveSheet()->SetCellValue('B3', 'NRIC');

	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
	$objPHPExcel->getActiveSheet()->mergeCells('C3:D3');
	$objPHPExcel->getActiveSheet()->SetCellValue('C3', 'Name');
	
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(70);
	$objPHPExcel->getActiveSheet()->mergeCells('E3:F3');
	$objPHPExcel->getActiveSheet()->SetCellValue('E3', 'Total No.of Activities Conducted in '.$month.' / '.$year);
	
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
	$objPHPExcel->getActiveSheet()->SetCellValue('G3', 'Total Attendance');
	
	$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(40);
	$objPHPExcel->getActiveSheet()->mergeCells('H3:I3');
	$objPHPExcel->getActiveSheet()->SetCellValue('H3', 'Overall Performance');
	
	//Second column Name
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
	$objPHPExcel->getActiveSheet()->mergeCells('C4:D4');
	$objPHPExcel->getActiveSheet()->SetCellValue('C4', '');
	
	if($category=="All"){
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(35);
		$objPHPExcel->getActiveSheet()->SetCellValue('E4', 'Physical Activities');
		
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(35);
		$objPHPExcel->getActiveSheet()->SetCellValue('F4', 'Social Activities');
		
		$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
		$objPHPExcel->getActiveSheet()->SetCellValue('H4', 'Physical Activities');
		
		$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
		$objPHPExcel->getActiveSheet()->SetCellValue('I4', 'Social Activities');
		
	}else{
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(40);
		$objPHPExcel->getActiveSheet()->mergeCells('E4:F4');
		$objPHPExcel->getActiveSheet()->SetCellValue('E4', $category.' Activities');
		
		$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(40);
		$objPHPExcel->getActiveSheet()->mergeCells('H4:I4');
		$objPHPExcel->getActiveSheet()->SetCellValue('H4', $category.' Activities');
	}
	
	//Input value into column
	if($category=='All'){
		$sno=1;
		for($i=0;$i<count($nricList);$i++){
			$NRIC=$nricList[$i];
			//first row set the column headings
			if($i==0){	
				$sql = "SELECT * from Client where NRIC=?";
				$parameters1=array($NRIC);
				$res1=$sqlDBManager->queryRow($sql,$parameters1);

				//client info
				if(!empty($res1)) {	
					$objPHPExcel->getActiveSheet()->mergeCells('A4:A5');
					$objPHPExcel->getActiveSheet()->SetCellValue('A4',$sno);
					$objPHPExcel->getActiveSheet()->mergeCells('B4:B5');
					$objPHPExcel->getActiveSheet()->SetCellValue('B4',$res1['NRIC']);
					$objPHPExcel->getActiveSheet()->mergeCells('C5:D5');
					$objPHPExcel->getActiveSheet()->SetCellValue('C5',$res1['CustomerName']);
				}

				$sql="SELECT COUNT( * ) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category = ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters2=array($NRIC,'Physical',$month,$year);
				$parameters3=array($NRIC,'Social',$month,$year);
				
				//total no of physical activities
				$res2=$sqlDBManager->queryRow($sql,$parameters2);
				
				//total no of social activities
				$res3=$sqlDBManager->queryRow($sql,$parameters3);
				
				//input data into physical column
				if(!empty($res2)){
					$objPHPExcel->getActiveSheet()->SetCellValue('E5', $res2[0]);
				}
				else{
					$objPHPExcel->getActiveSheet()->SetCellValue('E5', '0');
				}
				//input data into social column
				if(!empty($res3)){
					$objPHPExcel->getActiveSheet()->SetCellValue('F5', $res3[0]);
				}else{
					$objPHPExcel->getActiveSheet()->SetCellValue('F5', '0');
				}
				
				$sql="SELECT SUM(acr.Performance)/Count(*) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category =  ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters4=array($NRIC,'Physical',$month,$year);
				$parameters5=array($NRIC,'Social',$month,$year);
				
				//overall performance of physical activities
				$res4=$sqlDBManager->queryRow($sql,$parameters4);
				
				//overall performance of social activities
				$res5=$sqlDBManager->queryRow($sql,$parameters5);
				
				//input data into physical column
				if(!empty($res4)){
					$objPHPExcel->getActiveSheet()->SetCellValue('H5', $res4[0]);
				}
				else{
					$objPHPExcel->getActiveSheet()->SetCellValue('H5', '0');
				}
				//input data into social column
				if(!empty($res5)){
					$objPHPExcel->getActiveSheet()->SetCellValue('I5', $res5[0]);
				}else{
					$objPHPExcel->getActiveSheet()->SetCellValue('I5', '0');
				}
				//total attendance 
				$sql="SELECT COUNT( * ) FROM  attendancerecord att,  client cl WHERE cl.NRIC = ? AND cl.NRIC=att.NRIC AND MONTHNAME( att.SignOutTimeStamp ) =  ? AND YEAR( att.SignOutTimeStamp ) =  ? ;";
				$parameters6=array($NRIC,$month,$year);
				
				//total attendance	
				$res6=$sqlDBManager->queryRow($sql,$parameters6);
				if(!empty($res6)){
					$objPHPExcel->getActiveSheet()->mergeCells('G4:G5');
					$objPHPExcel->getActiveSheet()->SetCellValue('G4', $res6[0]);
				}
				else{
					$objPHPExcel->getActiveSheet()->mergeCells('G4:G5');
					$objPHPExcel->getActiveSheet()->SetCellValue('G4', '0');
				}
				$rowCount=6;
			}else{
				//Input value into column
				$sql = "SELECT * from Client where NRIC=?";
				$parameters1=array($NRIC);
				$res1=$sqlDBManager->queryRow($sql,$parameters1);
				
				if(!empty($res1)) {	
					$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount,$sno);
					$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount,$res1['NRIC']);
					$objPHPExcel->getActiveSheet()->mergeCells('C'.$rowCount.':D'.$rowCount);
					$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount,$res1['CustomerName']);
				}

				$sql="SELECT COUNT( * ) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category = ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters2=array($NRIC,'Physical',$month,$year);
				$parameters3=array($NRIC,'Social',$month,$year);
				
				//total no of physical activities
				$res2=$sqlDBManager->queryRow($sql,$parameters2);
				
				//total no of social activities
				$res3=$sqlDBManager->queryRow($sql,$parameters3);
				
				//input data into physical column
				if(!empty($res2)){
					$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, $res2[0]);
				}
				else{
					$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, '0');
				}
				//input data into social column
				if(!empty($res3)){
					$objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount, $res3[0]);
				}else{
					$objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount, '0');
				}
				
				$sql="SELECT SUM(acr.Performance)/Count(*) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category =  ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters4=array($NRIC,'Physical',$month,$year);
				$parameters5=array($NRIC,'Social',$month,$year);
				
				//overall performance of physical activities
				$res4=$sqlDBManager->queryRow($sql,$parameters4);
				
				//overall performance of social activities
				$res5=$sqlDBManager->queryRow($sql,$parameters5);
				
				//input data into physical column
				if(!empty($res4)){
					$objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount, $res4[0]);
				}
				else{
					$objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount, '0');
				}
				//input data into social column
				if(!empty($res5)){
					$objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount, $res5[0]);
				}else{
					$objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount, '0');
				}
				//total attendance 
				$sql="SELECT COUNT( * ) FROM  attendancerecord att,  client cl WHERE cl.NRIC = ? AND cl.NRIC=att.NRIC AND MONTHNAME( att.SignOutTimeStamp ) =  ? AND YEAR( att.SignOutTimeStamp ) =  ? ;";
				$parameters6=array($NRIC,$month,$year);
				
				//total attendance	
				$res6=$sqlDBManager->queryRow($sql,$parameters6);
				if(!empty($res6)){
					$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, $res6[0]);
				}
				else{
					$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, '0');
				}
				$rowCount++;
			}
			$sno++;
		}
	}else{
		$sno=1;
		for($i=0;$i<count($nricList);$i++){
			$NRIC=$nricList[$i];
			if($i==0){
				//Input value into column
				$sql = "SELECT * from Client where NRIC=?";
				$parameters1=array($NRIC);
				$res1=$sqlDBManager->queryRow($sql,$parameters1);
				
				if(!empty($res1)) {	
					$objPHPExcel->getActiveSheet()->mergeCells('A4:A5');
					$objPHPExcel->getActiveSheet()->SetCellValue('A4',$sno);
					$objPHPExcel->getActiveSheet()->mergeCells('B4:B5');
					$objPHPExcel->getActiveSheet()->SetCellValue('B4',$res1['NRIC']);
					$objPHPExcel->getActiveSheet()->mergeCells('C5:D5');
					$objPHPExcel->getActiveSheet()->SetCellValue('C5',$res1['CustomerName']);
				}

				$sql="SELECT COUNT( * ) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category = ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters2=array($NRIC,$category,$month,$year);
				
				//total no of category selected activities
				$res2=$sqlDBManager->queryRow($sql,$parameters2);
				
				//input data into category column
				if(!empty($res2)){
					$objPHPExcel->getActiveSheet()->mergeCells('E5:F5');
					$objPHPExcel->getActiveSheet()->SetCellValue('E5',$res2[0]);
				}
				else{
					$objPHPExcel->getActiveSheet()->mergeCells('E5:F5');
					$objPHPExcel->getActiveSheet()->SetCellValue('E5','0');
				}
				
				$sql="SELECT SUM(acr.Performance)/Count(*) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category =  ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters3=array($NRIC,$category,$month,$year);
				
				//overall performance of physical activities
				$res3=$sqlDBManager->queryRow($sql,$parameters3);
				
				//input data into physical column
				if(!empty($res3)){
					$objPHPExcel->getActiveSheet()->mergeCells('H5:I5');
					$objPHPExcel->getActiveSheet()->SetCellValue('H5',$res3[0]);
				}
				else{
					$objPHPExcel->getActiveSheet()->mergeCells('H5:I5');
					$objPHPExcel->getActiveSheet()->SetCellValue('H5','0');
				}
				//total attendance 
				$sql="SELECT COUNT( * ) FROM  attendancerecord att,  client cl WHERE cl.NRIC = ? AND cl.NRIC=att.NRIC AND MONTHNAME( att.SignOutTimeStamp ) =  ? AND YEAR( att.SignOutTimeStamp ) =  ? ;";
				$parameters6=array($NRIC,$month,$year);
				
				//total attendance	
				$res6=$sqlDBManager->queryRow($sql,$parameters6);
				if(!empty($res6)){
					$objPHPExcel->getActiveSheet()->mergeCells('G4:G5');
					$objPHPExcel->getActiveSheet()->SetCellValue('G4', $res6[0]);
				}
				else{
					$objPHPExcel->getActiveSheet()->mergeCells('G4:G5');
					$objPHPExcel->getActiveSheet()->SetCellValue('G4', '0');
				}
				$rowCount=6;
			}else{
				//Input value into column
				$sql = "SELECT * from Client where NRIC=?";
				$parameters1=array($NRIC);
				$res1=$sqlDBManager->queryRow($sql,$parameters1);
				
				if(!empty($res1)) {	
					$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount,$sno);
					$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount,$res1['NRIC']);
					$objPHPExcel->getActiveSheet()->mergeCells('C'.$rowCount.':D'.$rowCount);
					$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount,$res1['CustomerName']);
				}

				$sql="SELECT COUNT( * ) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category = ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters2=array($NRIC,$category,$month,$year);
				
				//total no of category selected activities
				$res2=$sqlDBManager->queryRow($sql,$parameters2);
				
				//input data into category column
				if(!empty($res2)){
					$objPHPExcel->getActiveSheet()->mergeCells('E'.$rowCount.':F'.$rowCount);
					$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount,$res2[0]);
				}
				else{
					$objPHPExcel->getActiveSheet()->mergeCells('E'.$rowCount.':F'.$rowCount);
					$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount,'0');
				}
				
				$sql="SELECT SUM(acr.Performance)/Count(*) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category =  ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters3=array($NRIC,$category,$month,$year);
				
				//overall performance of physical activities
				$res3=$sqlDBManager->queryRow($sql,$parameters3);
				
				//input data into physical column
				if(!empty($res3)){
					$objPHPExcel->getActiveSheet()->mergeCells('H'.$rowCount.':I'.$rowCount);
					$objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount,$res3[0]);
				}
				else{
					$objPHPExcel->getActiveSheet()->mergeCells('H'.$rowCount.':I'.$rowCount);
					$objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount,'0');
				}

				//total attendance 
				$sql="SELECT COUNT( * ) FROM  attendancerecord att,  client cl WHERE cl.NRIC = ? AND cl.NRIC=att.NRIC AND MONTHNAME( att.SignOutTimeStamp ) =  ? AND YEAR( att.SignOutTimeStamp ) =  ? ;";
				$parameters6=array($NRIC,$month,$year);
				
				//total attendance	
				$res6=$sqlDBManager->queryRow($sql,$parameters6);
				if(!empty($res6)){
					$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, $res6[0]);
				}
				else{
					$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, '0');
				}
				$rowCount++;
			}
			$sno++;
		}
	}
	
	//creating the excel file
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	header('Content-Type: application/vnd.ms-excel');
	header('Content-Disposition: attachment;filename="Eldercare Individual Report in '. $month . '/' . $year . '.xlsx"');
	header('Cache-Control: max-age=0');
	$objWriter->save('php://output');
	exit();
?>

